/**
 * 
 */
package wpmp.security.mgr;

/**
 * @author Wayne.Wang<5waynewang@gmail.com>
 * @since 4:03:26 PM May 5, 2014
 */
public interface SqlStatics {

	/**
	 * 根据用户名查询该用户有权限操作的资源
	 */
	String QUERY_USER_RESOUCE_OPERATION =
	"SELECT\n" +
	"	c.resource_id,\n" +
	"	d.operation_id,\n" +
	"	e.`level`,\n" +
	"	e.is_public\n" +
	"FROM\n" +
	"	sys_user a,\n" +
	"	sys_user_role_rela b,\n" +
	"	sys_role_resource_rela c,\n" +
	"	sys_role_resource_operation_rela d,\n" +
	"	sys_resource e\n" +
	"WHERE\n" +
	"	a.id = b.user_id\n" +
	"AND b.role_id = c.role_id\n" +
	"AND c.id = d.rela_id\n" +
	"AND c.resource_id = e.id\n" +
	"AND a.login_name = ?\n" +
	"UNION ALL\n" +
	"	SELECT\n" +
	"		b.resource_id,\n" +
	"		b.operation_id,\n" +
	"		a.`level`,\n" +
	"		a.is_public\n" +
	"	FROM\n" +
	"		sys_resource a,\n" +
	"		sys_resource_operation_rela b\n" +
	"	WHERE\n" +
	"		a.id = b.resource_id\n" +
	"	AND a.is_public = TRUE";
	
	/**
	 * 根据角色标识查询该角色有权限操作的资源
	 */
	String QUERY_ROLE_RESOUCE_OPERATION =
	"SELECT\n" +
	"	a.resource_id,\n" +
	"	b.operation_id,\n" +
	"	c.`level`,\n" +
	"	c.is_public\n" +
	"FROM\n" +
	"	sys_role_resource_rela a,\n" +
	"	sys_role_resource_operation_rela b,\n" +
	"	sys_resource c\n" +
	"WHERE\n" +
	"	a.id = b.rela_id\n" +
	"AND a.resource_id = c.id\n" +
	"AND a.role_id = ?";
	
	/**
	 * 新增组织/单位 根目录
	 */
	String INSERT_ROOT_ORG =
	"INSERT INTO sys_org (\n" +
	"	id,\n" +
	"	NAME,\n" +
	"	LEVEL,\n" +
	"	create_time,\n" +
	"	create_user,\n" +
	"	update_time,\n" +
	"	update_user\n" +
	") SELECT\n" +
	"	?,?,?,?,?,?,?\n" +
	"FROM\n" +
	"	DUAL\n" +
	"WHERE\n" +
	"	NOT EXISTS (\n" +
	"		SELECT\n" +
	"			1\n" +
	"		FROM\n" +
	"			sys_org\n" +
	"		WHERE\n" +
	"			id = ?\n" +
	"	)";
	
	/**
	 * 新增组织/单位
	 */
	String INSERT_ORG =
	"INSERT INTO sys_org (\n" +
	"	id,\n" +
	"	NAME,\n" +
	"	LEVEL,\n" +
	"	parent_id,\n" +
	"	create_time,\n" +
	"	create_user,\n" +
	"	update_time,\n" +
	"	update_user\n" +
	") SELECT\n" +
	"	?,?,?,?,?,?,?,?\n" +
	"FROM\n" +
	"	DUAL\n" +
	"WHERE\n" +
	"	NOT EXISTS (\n" +
	"		SELECT\n" +
	"			1\n" +
	"		FROM\n" +
	"			sys_org\n" +
	"		WHERE\n" +
	"			id = ?\n" +
	"	)";
	
	/**
	 * 新增角色 根目录
	 */
	String INSERT_ROOT_ROLE =
	"INSERT INTO sys_role (\n" +
	"	id,\n" +
	"	NAME,\n" +
	"	LEVEL,\n" +
	"	create_time,\n" +
	"	create_user,\n" +
	"	update_time,\n" +
	"	update_user\n" +
	") SELECT\n" +
	"	?,?,?,?,?,?,?\n" +
	"FROM\n" +
	"	DUAL\n" +
	"WHERE\n" +
	"	NOT EXISTS (\n" +
	"		SELECT\n" +
	"			1\n" +
	"		FROM\n" +
	"			sys_role\n" +
	"		WHERE\n" +
	"			id = ?\n" +
	"	)";
	
	/**
	 * 新增资源 根目录
	 */
	String INSERT_ROOT_RESOURCE =
	"INSERT INTO sys_resource (\n" +
	"	id,\n" +
	"	NAME,\n" +
	"	LEVEL,\n" +
	"	url,\n" +
	"	is_leaf,\n" +
	"	is_visible,\n" +
	"	is_public,\n" +
	"	need_login,\n" +
	"	create_time,\n" +
	"	create_user,\n" +
	"	update_time,\n" +
	"	update_user\n" +
	") SELECT\n" +
	"	?,?,?,?,?,?,?,?,?,?,?,?\n" +
	"FROM\n" +
	"	DUAL\n" +
	"WHERE\n" +
	"	NOT EXISTS (\n" +
	"		SELECT\n" +
	"			1\n" +
	"		FROM\n" +
	"			sys_resource\n" +
	"		WHERE\n" +
	"			id = ?\n" +
	"	)";

	/**
	 * 新增资源
	 */
	String INSERT_RESOURCE =
	"INSERT INTO sys_resource (\n" +
	"	id,\n" +
	"	NAME,\n" +
	"	LEVEL,\n" +
	"	url,\n" +
	"	is_leaf,\n" +
	"	is_visible,\n" +
	"	is_public,\n" +
	"	need_login,\n" +
	"	parent_id,\n" +
	"	order_num,\n" +
	"	base_url,\n" +
	"	base_action,\n" +
	"	para_url,\n" +
	"	create_time,\n" +
	"	create_user,\n" +
	"	update_time,\n" +
	"	update_user\n" +
	") SELECT\n" +
	"	?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?\n" +
	"FROM\n" +
	"	DUAL\n" +
	"WHERE\n" +
	"	NOT EXISTS (\n" +
	"		SELECT\n" +
	"			1\n" +
	"		FROM\n" +
	"			sys_resource\n" +
	"		WHERE\n" +
	"			id = ?\n" +
	"	)";
	
	/**
	 * 新增用户
	 */
	String INSERT_USER =
	"INSERT INTO sys_user (\n" +
	"	id,\n" +
	"	login_name,\n" +
	"	PASSWORD,\n" +
	"	user_name,\n" +
	"	is_admin,\n" +
	"	create_time,\n" +
	"	create_user,\n" +
	"	update_time,\n" +
	"	update_user\n" +
	") SELECT\n" +
	"	?,?,?,?,?,?,?,?,?\n" +
	"FROM\n" +
	"	DUAL\n" +
	"WHERE\n" +
	"	NOT EXISTS (\n" +
	"		SELECT\n" +
	"			1\n" +
	"		FROM\n" +
	"			sys_user\n" +
	"		WHERE\n" +
	"			id = ?\n" +
	"	)";
}
